

/**
	Author	    : Rohan
	Date	    : July 1st 2004
	Description : Deleted records of the address corresponding to the given customer number and ship to code 
		      from the following table
		      		1) Address
			        2) Address_Contact_Persons_Table
				3) Customer_Shipping_Address_Table
*/

alter proc sp_delete_epicore_address
(
@cust_number varchar(8),
@ship_to_code varchar(8)
)
as
begin

declare @address_id int
declare @cust_id int

--retreiving the customer ID
select @cust_id = Customer_Id from Customer where C_Number = @cust_number	
if( @@rowcount > 0 )
begin
	--retreiving the address_id 
	select @address_id = Address_ID from Address  (NOLOCK) where epicore_ship_code = @ship_to_code and ( exists( select 1 from Customer_Shipping_Address_Table (NOLOCK) where customer_id = @cust_id and address_id = address.address_id ) or exists ( select 1 from customer (NOLOCK) where customer_id = @cust_id and ( C_Default_Shipping_Address_ID = address.address_id or C_Default_Billing_Address_ID = address.address_id  ) ) ) 
	if( @@rowcount > 0 )
	begin
		--deleting records
		delete from Customer_Shipping_Address_Table where Address_ID = @address_id and Customer_ID = @cust_id
		delete from Address_Contact_Persons_Table where Address_ID = @address_id
		delete from address where Address_ID = @address_id
	end
end
end --End of proc